import pandas as pd
import numpy as np

def match_excel_files(herb_file, prep_file, output_file):
    # Read the Excel files
    herb_df = pd.read_excel(herb_file)
    prep_df = pd.read_excel(prep_file)
    
    # Round m/z column to 2 decimal places
    herb_df['m/z_rounded'] = herb_df['m/z'].round(2)
    prep_df['m/z_rounded'] = prep_df['m/z'].round(2)
    
    # Create empty list for storing matches
    matches = []
    
    # Group herb data by rounded m/z to speed up matching
    herb_groups = herb_df.groupby('m/z_rounded')
    
    # Process each preparation row
    for _, prep_row in prep_df.iterrows():
        prep_rt = prep_row['RT [min]']
        prep_mz = prep_row['m/z_rounded']
        
        # Check if this m/z exists in herb data
        if prep_mz in herb_groups.groups:
            # Get all herb rows with matching m/z
            matching_herbs = herb_groups.get_group(prep_mz)
            
            # Find herbs with RT difference < 0.5
            for _, herb_row in matching_herbs.iterrows():
                herb_rt = herb_row['RT [min]']
                
                if abs(herb_rt - prep_rt) < 0.5:
                    # Create a copy of the prep row with herb NO.
                    match_row = prep_row.copy()
                    match_row['Herb_NO'] = herb_row['NO.']
                    matches.append(match_row)
    
    # Create result dataframe and save
    if matches:
        result_df = pd.DataFrame(matches)
        # Remove the temporary column
        result_df = result_df.drop('m/z_rounded', axis=1)
        # Save to Excel
        result_df.to_excel(output_file, index=False)
        print(f"Found {len(matches)} matches. Results saved to {output_file}")
    else:
        print("No matches found.")


match_excel_files("./Data/药材.xlsx", "./Data/制剂一级.xlsx", "./Data/药材_一级.xlsx")